<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en-US">
<head>
<!-- GenHTML revision 25226-->
<meta http-equiv="Content-type" content="text/html; charset=utf-8">
<title>Full Query Language Syntax - The Java EE 6 Tutorial</title>
<meta name="robots" content="index,follow">
<meta name="robots" content="index,follow">
<meta name="date" content="2011-03-01">
<link rel="stylesheet" type="text/css" href="css/default.css">
<link rel="stylesheet" type="text/css" href="css/ipg.css">
<link rel="stylesheet" type="text/css" href="css/javaeetutorial.css">
</head>

<body>

<table border="0" cellpadding="5" cellspacing="0" width="100%">
<tbody>
   <tr valign="top">
      <td width="400px"><p class="toc level1"><a href="docinfo.html">Document Information</a></p>
<p class="toc level1 tocsp"><a href="gexaf.html">Preface</a></p>
<p class="toc level1 tocsp"><a href="gfirp.html">Part&nbsp;I&nbsp;Introduction</a></p>
<p class="toc level2"><a href="bnaaw.html">1.&nbsp;&nbsp;Overview</a></p>
<p class="toc level2"><a href="gfiud.html">2.&nbsp;&nbsp;Using the Tutorial Examples</a></p>
<p class="toc level1 tocsp"><a href="bnadp.html">Part&nbsp;II&nbsp;The Web Tier</a></p>
<p class="toc level2"><a href="bnadr.html">3.&nbsp;&nbsp;Getting Started with Web Applications</a></p>
<p class="toc level2"><a href="bnaph.html">4.&nbsp;&nbsp;JavaServer Faces Technology</a></p>
<p class="toc level2"><a href="giepx.html">5.&nbsp;&nbsp;Introduction to Facelets</a></p>
<p class="toc level2"><a href="gjddd.html">6.&nbsp;&nbsp;Expression Language</a></p>
<p class="toc level2"><a href="bnaqz.html">7.&nbsp;&nbsp;Using JavaServer Faces Technology in Web Pages</a></p>
<p class="toc level2"><a href="gjcut.html">8.&nbsp;&nbsp;Using Converters, Listeners, and Validators</a></p>
<p class="toc level2"><a href="bnatx.html">9.&nbsp;&nbsp;Developing with JavaServer Faces Technology</a></p>
<p class="toc level2"><a href="gkmaa.html">10.&nbsp;&nbsp;JavaServer Faces Technology Advanced Concepts</a></p>
<p class="toc level2"><a href="bnawo.html">11.&nbsp;&nbsp;Configuring JavaServer Faces Applications</a></p>
<p class="toc level2"><a href="gkiow.html">12.&nbsp;&nbsp;Using Ajax with JavaServer Faces Technology</a></p>
<p class="toc level2"><a href="gkhxa.html">13.&nbsp;&nbsp;Advanced Composite Components</a></p>
<p class="toc level2"><a href="bnavg.html">14.&nbsp;&nbsp;Creating Custom UI Components</a></p>
<p class="toc level2"><a href="bnafd.html">15.&nbsp;&nbsp;Java Servlet Technology</a></p>
<p class="toc level2"><a href="bnaxu.html">16.&nbsp;&nbsp;Internationalizing and Localizing Web Applications</a></p>
<p class="toc level1 tocsp"><a href="bnayk.html">Part&nbsp;III&nbsp;Web Services</a></p>
<p class="toc level2"><a href="gijti.html">17.&nbsp;&nbsp;Introduction to Web Services</a></p>
<p class="toc level2"><a href="bnayl.html">18.&nbsp;&nbsp;Building Web Services with JAX-WS</a></p>
<p class="toc level2"><a href="giepu.html">19.&nbsp;&nbsp;Building RESTful Web Services with JAX-RS</a></p>
<p class="toc level2"><a href="gjjxe.html">20.&nbsp;&nbsp;Advanced JAX-RS Features</a></p>
<p class="toc level2"><a href="gkojl.html">21.&nbsp;&nbsp;Running the Advanced JAX-RS Example Application</a></p>
<p class="toc level1 tocsp"><a href="bnblr.html">Part&nbsp;IV&nbsp;Enterprise Beans</a></p>
<p class="toc level2"><a href="gijsz.html">22.&nbsp;&nbsp;Enterprise Beans</a></p>
<p class="toc level2"><a href="gijre.html">23.&nbsp;&nbsp;Getting Started with Enterprise Beans</a></p>
<p class="toc level2"><a href="gijrb.html">24.&nbsp;&nbsp;Running the Enterprise Bean Examples</a></p>
<p class="toc level2"><a href="bnbpk.html">25.&nbsp;&nbsp;A Message-Driven Bean Example</a></p>
<p class="toc level2"><a href="gkcqz.html">26.&nbsp;&nbsp;Using the Embedded Enterprise Bean Container</a></p>
<p class="toc level2"><a href="gkidz.html">27.&nbsp;&nbsp;Using Asynchronous Method Invocation in Session Beans</a></p>
<p class="toc level1 tocsp"><a href="gjbnr.html">Part&nbsp;V&nbsp;Contexts and Dependency Injection for the Java EE Platform</a></p>
<p class="toc level2"><a href="giwhb.html">28.&nbsp;&nbsp;Introduction to Contexts and Dependency Injection for the Java EE Platform</a></p>
<p class="toc level2"><a href="gjbls.html">29.&nbsp;&nbsp;Running the Basic Contexts and Dependency Injection Examples</a></p>
<p class="toc level2"><a href="gjehi.html">30.&nbsp;&nbsp;Contexts and Dependency Injection for the Java EE Platform: Advanced Topics</a></p>
<p class="toc level2"><a href="gkhre.html">31.&nbsp;&nbsp;Running the Advanced Contexts and Dependency Injection Examples</a></p>
<p class="toc level1 tocsp"><a href="bnbpy.html">Part&nbsp;VI&nbsp;Persistence</a></p>
<p class="toc level2"><a href="bnbpz.html">32.&nbsp;&nbsp;Introduction to the Java Persistence API</a></p>
<p class="toc level2"><a href="gijst.html">33.&nbsp;&nbsp;Running the Persistence Examples</a></p>
<p class="toc level2"><a href="bnbtg.html">34.&nbsp;&nbsp;The Java Persistence Query Language</a></p>
<p class="toc level3"><a href="bnbth.html">Query Language Terminology</a></p>
<p class="toc level3"><a href="bnbrg.html">Creating Queries Using the Java Persistence Query Language</a></p>
<p class="toc level4"><a href="bnbrg.html#bnbrh">Named Parameters in Queries</a></p>
<p class="toc level4"><a href="bnbrg.html#bnbri">Positional Parameters in Queries</a></p>
<p class="toc level3 tocsp"><a href="bnbti.html">Simplified Query Language Syntax</a></p>
<p class="toc level4"><a href="bnbti.html#bnbtj">Select Statements</a></p>
<p class="toc level4"><a href="bnbti.html#bnbtk">Update and Delete Statements</a></p>
<p class="toc level3 tocsp"><a href="bnbtl.html">Example Queries</a></p>
<p class="toc level4"><a href="bnbtl.html#bnbtm">Simple Queries</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbtn">A Basic Select Query</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbto">Eliminating Duplicate Values</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbtp">Using Named Parameters</a></p>
<p class="toc level4 tocsp"><a href="bnbtl.html#bnbtq">Queries That Navigate to Related Entities</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbtr">A Simple Query with Relationships</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbts">Navigating to Single-Valued Relationship Fields</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbtt">Traversing Relationships with an Input Parameter</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbtu">Traversing Multiple Relationships</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbtv">Navigating According to Related Fields</a></p>
<p class="toc level4 tocsp"><a href="bnbtl.html#bnbtw">Queries with Other Conditional Expressions</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbtx">The <tt>LIKE</tt> Expression</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbty">The <tt>IS NULL</tt> Expression</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbtz">The <tt>IS EMPTY</tt> Expression</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbua">The <tt>BETWEEN</tt> Expression</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbub">Comparison Operators</a></p>
<p class="toc level4 tocsp"><a href="bnbtl.html#bnbuc">Bulk Updates and Deletes</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbud">Update Queries</a></p>
<p class="toc level5"><a href="bnbtl.html#bnbue">Delete Queries</a></p>
<div id="scrolltoc" class="onpage">
<p class="toc level3 tocsp"><a href="">Full Query Language Syntax</a></p>
<p class="toc level4"><a href="#bnbug">BNF Symbols</a></p>
<p class="toc level4"><a href="#bnbui">BNF Grammar of the Java Persistence Query Language</a></p>
<p class="toc level4"><a href="#bnbuj"><tt>FROM</tt> Clause</a></p>
<p class="toc level5"><a href="#bnbuk">Identifiers</a></p>
<p class="toc level5"><a href="#bnbum">Identification Variables</a></p>
<p class="toc level5"><a href="#bnbun">Range Variable Declarations</a></p>
<p class="toc level5"><a href="#bnbuo">Collection Member Declarations</a></p>
<p class="toc level5"><a href="#bnbup">Joins</a></p>
<p class="toc level4 tocsp"><a href="#bnbuq">Path Expressions</a></p>
<p class="toc level5"><a href="#bnbur">Examples of Path Expressions</a></p>
<p class="toc level5"><a href="#bnbus">Expression Types</a></p>
<p class="toc level5"><a href="#bnbut">Navigation</a></p>
<p class="toc level4 tocsp"><a href="#bnbuu"><tt>WHERE</tt> Clause</a></p>
<p class="toc level5"><a href="#bnbuv">Literals</a></p>
<p class="toc level5"><a href="#bnbva">Input Parameters</a></p>
<p class="toc level5"><a href="#bnbvb">Conditional Expressions</a></p>
<p class="toc level5"><a href="#bnbvc">Operators and Their Precedence</a></p>
<p class="toc level5"><a href="#bnbve"><tt>BETWEEN</tt> Expressions</a></p>
<p class="toc level5"><a href="#bnbvf"><tt>IN</tt> Expressions</a></p>
<p class="toc level5"><a href="#bnbvg"><tt>LIKE</tt> Expressions</a></p>
<p class="toc level5"><a href="#bnbvi"><tt>NULL</tt> Comparison Expressions</a></p>
<p class="toc level5"><a href="#bnbvj">Empty Collection Comparison Expressions</a></p>
<p class="toc level5"><a href="#bnbvk">Collection Member Expressions</a></p>
<p class="toc level5"><a href="#bnbvl">Subqueries</a></p>
<p class="toc level5"><a href="#bnbvo">Functional Expressions</a></p>
<p class="toc level5"><a href="#gjjnd">Case Expressions</a></p>
<p class="toc level5"><a href="#bnbvr">NULL Values</a></p>
<p class="toc level5"><a href="#bnbvu">Equality Semantics</a></p>
<p class="toc level4 tocsp"><a href="#bnbvx"><tt>SELECT</tt> Clause</a></p>
<p class="toc level5"><a href="#bnbvy">Return Types</a></p>
<p class="toc level5"><a href="#bnbwb">The <tt>DISTINCT</tt> Keyword</a></p>
<p class="toc level5"><a href="#bnbwc">Constructor Expressions</a></p>
<p class="toc level4 tocsp"><a href="#bnbwd"><tt>ORDER BY</tt> Clause</a></p>
<p class="toc level4"><a href="#bnbwe"><tt>GROUP BY</tt> and <tt>HAVING</tt> Clauses</a></p>
</div>
<p class="toc level2 tocsp"><a href="gjitv.html">35.&nbsp;&nbsp;Using the Criteria API to Create Queries</a></p>
<p class="toc level2"><a href="gkjiq.html">36.&nbsp;&nbsp;Creating and Using String-Based Criteria Queries</a></p>
<p class="toc level2"><a href="gkjjf.html">37.&nbsp;&nbsp;Controlling Concurrent Access to Entity Data with Locking</a></p>
<p class="toc level2"><a href="gkjia.html">38.&nbsp;&nbsp;Improving the Performance of Java Persistence API Applications By Setting a Second-Level Cache</a></p>
<p class="toc level1 tocsp"><a href="gijrp.html">Part&nbsp;VII&nbsp;Security</a></p>
<p class="toc level2"><a href="bnbwj.html">39.&nbsp;&nbsp;Introduction to Security in the Java EE Platform</a></p>
<p class="toc level2"><a href="bncas.html">40.&nbsp;&nbsp;Getting Started Securing Web Applications</a></p>
<p class="toc level2"><a href="bnbyk.html">41.&nbsp;&nbsp;Getting Started Securing Enterprise Applications</a></p>
<p class="toc level1 tocsp"><a href="gijue.html">Part&nbsp;VIII&nbsp;Java EE Supporting Technologies</a></p>
<p class="toc level2"><a href="gijto.html">42.&nbsp;&nbsp;Introduction to Java EE Supporting Technologies</a></p>
<p class="toc level2"><a href="bncih.html">43.&nbsp;&nbsp;Transactions</a></p>
<p class="toc level2"><a href="bncjh.html">44.&nbsp;&nbsp;Resource Connections</a></p>
<p class="toc level2"><a href="bncdq.html">45.&nbsp;&nbsp;Java Message Service Concepts</a></p>
<p class="toc level2"><a href="bncgv.html">46.&nbsp;&nbsp;Java Message Service Examples</a></p>
<p class="toc level2"><a href="gkahp.html">47.&nbsp;&nbsp;Advanced Bean Validation Concepts and Examples</a></p>
<p class="toc level2"><a href="gkeed.html">48.&nbsp;&nbsp;Using Java EE Interceptors</a></p>
<p class="toc level1 tocsp"><a href="gkgjw.html">Part&nbsp;IX&nbsp;Case Studies</a></p>
<p class="toc level2"><a href="gkaee.html">49.&nbsp;&nbsp;Duke's Tutoring Case Study Example</a></p>
<p class="toc level1 tocsp"><a href="idx-1.html">Index</a></p>
</td>
      <td width="10px">&nbsp;</td>
      <td>
         <div class="header">
             <div class="banner">
                <table width="100%" border="0" cellpadding="5" cellspacing="0">
                   <tbody>
                      <tr>
                         <td valign="bottom"><p class="Banner">The Java EE 6 Tutorial
</p></td>
                         <td align="right"  valign="bottom"><img src="graphics/javalogo.png" alt="Java Coffee Cup logo"></td>
                      </tr>
                   </tbody>
                </table>
             </div>

             <div class="header-links">
	         <a href="./index.html">Home</a> | 
<a href="../information/download.html">Download</a> | 
<a href="./javaeetutorial6.pdf">PDF</a> | 
<a href="../information/faq.html">FAQ</a> | 
<a href="http://download.oracle.com/javaee/feedback.htm">Feedback</a>

             </div>
             <div class="navigation">
                 <a href="bnbtl.html"><img src="graphics/leftButton.gif" border="0" alt="Previous" title="Previous"></a>
                 <a href="p1.html"><img src="graphics/upButton.gif" border="0" alt="Contents" title="Contents"></a>
                 <a href="gjitv.html"><img src="graphics/rightButton.gif" border="0" alt="Next" title="Next"></a>
             </div>
         </div>

	 <div class="maincontent">      	 
             

<a name="bnbuf"></a><h2>Full Query Language Syntax</h2>
<a name="indexterm-1767"></a><a name="indexterm-1768"></a><p>This section discusses the query language syntax, as defined in the Java Persistence
API 2.0 specification available at <a href="http://jcp.org/en/jsr/detail?id=317">http://jcp.org/en/jsr/detail?id=317</a>. Much of the following material paraphrases or
directly quotes the specification.</p>



<a name="bnbug"></a><h3>BNF Symbols</h3>
<p><a href="#bnbuh">Table&nbsp;34-1</a> describes the BNF symbols used in this chapter.</p>

<a name="bnbuh"></a><p class="caption">Table&nbsp;34-1 BNF Symbol Summary</p><table><col width="16%"><col width="83%"><tr><th align="left" valign="top" scope="column"><p>Symbol</p>

</th>
<th align="left" valign="top" scope="column"><p>Description</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>::=</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>The element to the left
of the symbol is defined by the constructs on the right.</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>*</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>The preceding construct
may occur zero or more times.</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>{...}</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>The constructs within the braces are grouped
together.</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>[...]</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>The constructs within the brackets are optional.</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>|</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>An exclusive <tt>OR</tt>.</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>BOLDFACE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>A keyword; although capitalized
in the BNF diagram, keywords are not case-sensitive.</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>White space</p>

</td>
<td align="left" valign="top" scope="row"><p>A whitespace character can be
a space, a horizontal tab, or a line feed.</p>

</td>
</tr>
</table>

<a name="bnbui"></a><h3>BNF Grammar of the Java Persistence Query Language</h3>
<p>Here is the entire BNF diagram for the query language:</p>

<pre>QL_statement ::= select_statement | update_statement | delete_statement
select_statement ::= select_clause from_clause [where_clause] [groupby_clause] 
    [having_clause] [orderby_clause]
update_statement ::= update_clause [where_clause]
delete_statement ::= delete_clause [where_clause]
from_clause ::=
    FROM identification_variable_declaration
        {, {identification_variable_declaration |
            collection_member_declaration}}*
identification_variable_declaration ::=
        range_variable_declaration { join | fetch_join }*
range_variable_declaration ::= abstract_schema_name [AS]
        identification_variable
join ::= join_spec join_association_path_expression [AS]
        identification_variable
fetch_join ::= join_specFETCH join_association_path_expression
association_path_expression ::=
        collection_valued_path_expression |
        single_valued_association_path_expression
join_spec::= [LEFT [OUTER] |INNER] JOIN
join_association_path_expression ::=
        join_collection_valued_path_expression |
        join_single_valued_association_path_expression
join_collection_valued_path_expression::=
    identification_variable.collection_valued_association_field
join_single_valued_association_path_expression::=
        identification_variable.single_valued_association_field
collection_member_declaration ::=
        IN (collection_valued_path_expression) [AS]
        identification_variable
single_valued_path_expression ::=
        state_field_path_expression |
        single_valued_association_path_expression
state_field_path_expression ::=
    {identification_variable |
    single_valued_association_path_expression}.state_field
single_valued_association_path_expression ::=
    identification_variable.{single_valued_association_field.}*
    single_valued_association_field
collection_valued_path_expression ::=
    identification_variable.{single_valued_association_field.}*
    collection_valued_association_field
state_field ::=
    {embedded_class_state_field.}*simple_state_field
update_clause ::=UPDATE abstract_schema_name [[AS]
    identification_variable] SET update_item {, update_item}*
update_item ::= [identification_variable.]{state_field |
    single_valued_association_field} = new_value
new_value ::=
     simple_arithmetic_expression |
    string_primary |
    datetime_primary |
    boolean_primary |
    enum_primary simple_entity_expression |
    NULL
delete_clause ::= DELETE FROM abstract_schema_name [[AS]
    identification_variable]
select_clause ::= SELECT [DISTINCT] select_expression {,
    select_expression}*
select_expression ::=
    single_valued_path_expression |
    aggregate_expression |
    identification_variable |
    OBJECT(identification_variable) |
    constructor_expression
constructor_expression ::=
    NEW constructor_name(constructor_item {,
    constructor_item}*)
constructor_item ::= single_valued_path_expression |
    aggregate_expression
aggregate_expression ::=
    {AVG |MAX |MIN |SUM} ([DISTINCT]
        state_field_path_expression) |
    COUNT ([DISTINCT] identification_variable |
        state_field_path_expression |
        single_valued_association_path_expression)
where_clause ::= WHERE conditional_expression
groupby_clause ::= GROUP BY groupby_item {, groupby_item}*
groupby_item ::= single_valued_path_expression
having_clause ::= HAVING conditional_expression
orderby_clause ::= ORDER BY orderby_item {, orderby_item}*
orderby_item ::= state_field_path_expression [ASC |DESC]
subquery ::= simple_select_clause subquery_from_clause
    [where_clause] [groupby_clause] [having_clause]
subquery_from_clause ::=
    FROM subselect_identification_variable_declaration
        {, subselect_identification_variable_declaration}*
subselect_identification_variable_declaration ::=
    identification_variable_declaration |
    association_path_expression [AS] identification_variable |
    collection_member_declaration
simple_select_clause ::= SELECT [DISTINCT]
    simple_select_expression
simple_select_expression::=
    single_valued_path_expression |
    aggregate_expression |
    identification_variable
conditional_expression ::= conditional_term |
    conditional_expression OR conditional_term
conditional_term ::= conditional_factor | conditional_term AND
    conditional_factor
conditional_factor ::= [NOT] conditional_primary
conditional_primary ::= simple_cond_expression |(
    conditional_expression)
simple_cond_expression ::=
    comparison_expression |
    between_expression |
    like_expression |
    in_expression |
    null_comparison_expression |
    empty_collection_comparison_expression |
    collection_member_expression |
    exists_expression
between_expression ::=
    arithmetic_expression [NOT] BETWEEN
        arithmetic_expressionAND arithmetic_expression |
    string_expression [NOT] BETWEEN string_expression AND
        string_expression |
    datetime_expression [NOT] BETWEEN
        datetime_expression AND datetime_expression
in_expression ::=
    state_field_path_expression [NOT] IN (in_item {, in_item}*
    | subquery)
in_item ::= literal | input_parameter
like_expression ::=
    string_expression [NOT] LIKE pattern_value [ESCAPE
        escape_character]
null_comparison_expression ::=
    {single_valued_path_expression | input_parameter} IS [NOT]
        NULL
empty_collection_comparison_expression ::=
    collection_valued_path_expression IS [NOT] EMPTY
collection_member_expression ::= entity_expression
    [NOT] MEMBER [OF] collection_valued_path_expression
exists_expression::= [NOT] EXISTS (subquery)
all_or_any_expression ::= {ALL |ANY |SOME} (subquery)
comparison_expression ::=
    string_expression comparison_operator {string_expression |
    all_or_any_expression} |
    boolean_expression {= |&lt;> } {boolean_expression |
    all_or_any_expression} |
    enum_expression {= |&lt;> } {enum_expression |
    all_or_any_expression} |
    datetime_expression comparison_operator
        {datetime_expression | all_or_any_expression} |
    entity_expression {= |&lt;> } {entity_expression |
    all_or_any_expression} |
    arithmetic_expression comparison_operator
        {arithmetic_expression | all_or_any_expression}
comparison_operator ::= = |> |>= |&lt; |&lt;= |&lt;>
arithmetic_expression ::= simple_arithmetic_expression |
    (subquery)
simple_arithmetic_expression ::=
    arithmetic_term | simple_arithmetic_expression {+ |- }
        arithmetic_term
arithmetic_term ::= arithmetic_factor | arithmetic_term {* |/ }
    arithmetic_factor
arithmetic_factor ::= [{+ |- }] arithmetic_primary
arithmetic_primary ::=
    state_field_path_expression |
    numeric_literal |
    (simple_arithmetic_expression) |
    input_parameter |
    functions_returning_numerics |
    aggregate_expression
string_expression ::= string_primary | (subquery)
string_primary ::=
    state_field_path_expression |
    string_literal |
    input_parameter |
    functions_returning_strings |
    aggregate_expression
datetime_expression ::= datetime_primary | (subquery)
datetime_primary ::=
    state_field_path_expression |
    input_parameter |
    functions_returning_datetime |
    aggregate_expression
boolean_expression ::= boolean_primary | (subquery)
boolean_primary ::=
    state_field_path_expression |
    boolean_literal |
    input_parameter
 enum_expression ::= enum_primary | (subquery)
enum_primary ::=
    state_field_path_expression |
    enum_literal |
    input_parameter
entity_expression ::=
    single_valued_association_path_expression |
        simple_entity_expression
simple_entity_expression ::=
    identification_variable |
    input_parameter
functions_returning_numerics::=
    LENGTH(string_primary) |
    LOCATE(string_primary, string_primary[,
        simple_arithmetic_expression]) |
    ABS(simple_arithmetic_expression) |
    SQRT(simple_arithmetic_expression) |
    MOD(simple_arithmetic_expression,
        simple_arithmetic_expression) |
    SIZE(collection_valued_path_expression)
functions_returning_datetime ::=
    CURRENT_DATE |
    CURRENT_TIME |
    CURRENT_TIMESTAMP
functions_returning_strings ::=
    CONCAT(string_primary, string_primary) |
    SUBSTRING(string_primary,
        simple_arithmetic_expression,
        simple_arithmetic_expression)|
    TRIM([[trim_specification] [trim_character] FROM]
        string_primary) |
    LOWER(string_primary) |
    UPPER(string_primary)
trim_specification ::= LEADING | TRAILING | BOTH</pre>

<a name="bnbuj"></a><h3><tt>FROM</tt> Clause</h3>
<a name="indexterm-1769"></a><p><a name="indexterm-1770"></a><a name="indexterm-1771"></a>The <tt>FROM</tt> clause defines the domain of the query by declaring identification variables.</p>



<a name="bnbuk"></a><h4>Identifiers</h4>
<a name="indexterm-1772"></a><p>An identifier is a sequence of one or more characters. The first
character must be a valid first character (letter, <tt>$</tt>, <tt>_</tt>) in an identifier of
the Java programming language, hereafter in this chapter called simply &ldquo;Java&rdquo;. Each subsequent
character in the sequence must be a valid nonfirst character (letter, digit, <tt>$</tt>,
<tt>_</tt>) in a Java identifier. (For details, see the Java SE API
documentation of the <tt>isJavaIdentifierStart</tt> and <tt>isJavaIdentifierPart</tt> methods of the <tt>Character</tt> class.) The question mark
(<tt>?</tt>) is a reserved character in the query language and cannot be used
in an identifier.</p>

<p>A query language identifier is case-sensitive, with two exceptions:</p>


<ul><li><p>Keywords</p>

</li>
<li><p>Identification variables</p>

</li></ul>
<p>An identifier cannot be the same as a query language keyword. Here
is a list of query language keywords:</p>

<table><col width="25%"><col width="25%"><col width="25%"><col width="25%"><tr><td align="left" valign="top" scope="row"><p><tt>ABS</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>ALL</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>AND</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>ANY</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>AS</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>ASC</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>AVG</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>BETWEEN</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>BIT_LENGTH</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>BOTH</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>BY</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>CASE</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>CHAR_LENGTH</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>CHARACTER_LENGTH</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>CLASS</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>COALESCE</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>CONCAT</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>COUNT</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>CURRENT_DATE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>CURRENT_TIMESTAMP</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>DELETE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>DESC</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>DISTINCT</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>ELSE</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>EMPTY</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>END</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>ENTRY</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>ESCAPE</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>EXISTS</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>FALSE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>FETCH</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>FROM</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>GROUP</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>HAVING</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>IN</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>INDEX</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>INNER</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>IS</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>JOIN</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>KEY</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>LEADING</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>LEFT</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>LENGTH</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>LIKE</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>LOCATE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>LOWER</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>MAX</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>MEMBER</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>MIN</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>MOD</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>NEW</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>NOT</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>NULL</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>NULLIF</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>OBJECT</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>OF</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>OR</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>ORDER</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>OUTER</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>POSITION</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>SELECT</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>SET</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>SIZE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>SOME</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>SQRT</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>SUBSTRING</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>SUM</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>THEN</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>TRAILING</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>TRIM</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>TRUE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>TYPE</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>UNKNOWN</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>UPDATE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>UPPER</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>VALUE</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>WHEN</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>WHERE</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"></td>
<td align="left" valign="top" scope="row"></td>
<td align="left" valign="top" scope="row"></td>
<td align="left" valign="top" scope="row"></td>
</tr>
</table><p><a name="indexterm-1773"></a><a name="indexterm-1774"></a>It is not recommended that you use an SQL keyword as an identifier,
because the list of keywords may expand to include other reserved SQL words
in the future.</p>



<a name="bnbum"></a><h4>Identification Variables</h4>
<a name="indexterm-1775"></a><p>An <b>identification variable</b> is an identifier declared in the <tt>FROM</tt> clause. Although they can
reference identification variables, the <tt>SELECT</tt> and <tt>WHERE</tt> clauses cannot declare them. All identification variables
must be declared in the <tt>FROM</tt> clause.</p>

<p><a name="indexterm-1776"></a><a name="indexterm-1777"></a>Because it is an identifier, an identification variable has the same naming conventions
and restrictions as an identifier, with the exception that an identification variables is
case-insensitive. For example, an identification variable cannot be the same as a query
language keyword. (See the preceding section for more naming rules.) Also, within a given
persistence unit, an identification variable name must not match the name of any
entity or abstract schema.</p>

<p><a name="indexterm-1778"></a>The <tt>FROM</tt> clause can contain multiple declarations, separated by commas. A declaration can
reference another identification variable that has been previously declared (to the left). In
the following <tt>FROM</tt> clause, the variable <tt>t</tt> references the previously declared variable <tt>p</tt>:</p>

<pre>FROM Player p, IN (p.teams) AS t</pre><p><a name="indexterm-1779"></a>Even if it is not used in the <tt>WHERE</tt> clause, an identification variable's
declaration can affect the results of the query. For example, compare the next
two queries. The following query returns all players, whether or not they belong
to a team:</p>

<pre>SELECT p
FROM Player p</pre><p>In contrast, because it declares the <tt>t</tt> identification variable, the next query fetches
all players who belong to a team:</p>

<pre>SELECT p
FROM Player p, IN (p.teams) AS t</pre><p>The following query returns the same results as the preceding query, but the
<tt>WHERE</tt> clause makes it easier to read:</p>

<pre>SELECT p
FROM Player p
WHERE p.teams IS NOT EMPTY</pre><p>An identification variable always designates a reference to a single value whose type
is that of the expression used in the declaration. There are two kinds
of declarations: range variable and collection member.</p>



<a name="bnbun"></a><h4>Range Variable Declarations</h4>
<a name="indexterm-1780"></a><p><a name="indexterm-1781"></a>To declare an identification variable as an abstract schema type, you specify a
range variable declaration. In other words, an identification variable can range over the
abstract schema type of an entity. In the following example, an identification variable
named <tt>p</tt> represents the abstract schema named <tt>Player</tt>:</p>

<pre>FROM Player p</pre><p>A range variable declaration can include the optional <tt>AS</tt> operator:</p>

<pre>FROM Player AS p</pre><p><a name="indexterm-1782"></a><a name="indexterm-1783"></a>To obtain objects, a query usually uses path expressions to navigate through the
relationships. But for those objects that cannot be obtained by navigation, you can
use a range variable declaration to designate a starting point, or <b>root</b>.</p>

<p>If the query compares multiple values of the same abstract schema type, the
<tt>FROM</tt> clause must declare multiple identification variables for the abstract schema:</p>

<pre>FROM Player p1, Player p2</pre><p>For an example of such a query, see <a href="bnbtl.html#bnbub">Comparison Operators</a>.</p>



<a name="bnbuo"></a><h4>Collection Member Declarations</h4>
<a name="indexterm-1784"></a><a name="indexterm-1785"></a><p><a name="indexterm-1786"></a>In a one-to-many relationship, the multiple side consists of a collection of entities.
An identification variable can represent a member of this collection. To access a
collection member, the path expression in the variable&rsquo;s declaration navigates through the relationships in
the abstract schema. (For more information on path expressions, see <a href="#bnbuq">Path Expressions</a>.) Because a
path expression can be based on another path expression, the navigation can traverse
several relationships. See <a href="bnbtl.html#bnbtu">Traversing Multiple Relationships</a>.</p>

<p>A collection member declaration must include the <tt>IN</tt> operator but can omit the
optional <tt>AS</tt> operator.</p>

<p>In the following example, the entity represented by the abstract schema named <tt>Player</tt>
has a relationship field called <tt>teams</tt>. The identification variable called <tt>t</tt> represents a
single member of the <tt>teams</tt> collection.</p>

<pre>FROM Player p, IN (p.tea
ms) t</pre>

<a name="bnbup"></a><h4>Joins</h4>
<a name="indexterm-1787"></a><p>The <tt>JOIN</tt> operator is used to traverse over relationships between entities and is
functionally similar to the <tt>IN</tt> operator.</p>

<p>In the following example, the query joins over the relationship between customers and
orders:</p>

<pre>SELECT c
 FROM Customer c JOIN c.orders o
 WHERE c.status = 1 AND o.totalPrice > 10000</pre><p><a name="indexterm-1788"></a>The <tt>INNER</tt> keyword is optional:</p>

<pre>SELECT c
 FROM Customer c INNER JOIN c.orders o
 WHERE c.status = 1 AND o.totalPrice > 10000</pre><p><a name="indexterm-1789"></a>These examples are equivalent to the following query, which uses the <tt>IN</tt> operator:</p>

<pre>SELECT c
 FROM Customer c, IN(c.orders) o
 WHERE c.status = 1 AND o.totalPrice > 10000</pre><p>You can also join a single-valued relationship:</p>

<pre>SELECT t
 FROM Team t JOIN t.league l
 WHERE l.sport = :sport</pre><p><a name="indexterm-1790"></a><a name="indexterm-1791"></a>A <tt>LEFT JOIN</tt> or <tt>LEFT OUTER JOIN</tt> retrieves a set of entities where matching values in
the join condition may be absent. The <tt>OUTER</tt> keyword is optional.</p>

<pre>SELECT c.name, o.totalPrice
FROM Order o LEFT JOIN o.customer c</pre><p><a name="indexterm-1792"></a>A <tt>FETCH JOIN</tt> is a join operation that returns associated entities as a side
effect of running the query. In the following example, the query returns a
set of departments and, as a side effect, the associated employees of the
departments, even though the employees were not explicitly retrieved by the <tt>SELECT</tt> clause.</p>

<pre>SELECT d
FROM Department d LEFT JOIN FETCH d.employees
WHERE d.deptno = 1</pre>

<a name="bnbuq"></a><h3>Path Expressions</h3>
<a name="indexterm-1793"></a><p><a name="indexterm-1794"></a><a name="indexterm-1795"></a>Path expressions are important constructs in the syntax of the query language, for
several reasons. First, path expressions define navigation paths through the relationships in the abstract
schema. These path definitions affect both the scope and the results of a
query. Second, path expressions can appear in any of the main clauses of
a query (<tt>SELECT</tt>, <tt>DELETE</tt>, <tt>HAVING</tt>, <tt>UPDATE</tt>, <tt>WHERE</tt>, <tt>FROM</tt>, <tt>GROUP BY</tt>, <tt>ORDER BY</tt>). Finally, although much
of the query language is a subset of SQL, path expressions are extensions
not found in SQL.</p>



<a name="bnbur"></a><h4>Examples of Path Expressions</h4>
<p>Here, the <tt>WHERE</tt> clause contains a <tt>single_valued_path_expression</tt>; the <tt>p</tt> is an identification
variable, and <tt>salary</tt> is a persistent field of <tt>Player</tt>:</p>

<pre>SELECT DISTINCT p
FROM Player p
 WHERE p.salary BETWEEN :lowerSalary AND :higherSalary</pre><p>Here, the <tt>WHERE</tt> clause also contains a <tt>single_valued_path_expression</tt>; <tt>t</tt> is an identification
variable, <tt>league</tt> is a single-valued relationship field, and <tt>sport</tt> is a persistent field of
<tt>league</tt>:</p>

<pre>SELECT DISTINCT p
FROM Player p, IN (p.teams) t
 WHERE t.league.sport = :sport</pre><p>Here, the <tt>WHERE</tt> clause contains a <tt>collection_valued_path_expression</tt>; <tt>p</tt> is an identification variable,
and <tt>teams</tt> designates a collection-valued relationship field:</p>

<pre>SELECT DISTINCT p
FROM Player p
 WHERE p.teams IS EMPTY</pre>

<a name="bnbus"></a><h4>Expression Types</h4>
<a name="indexterm-1796"></a><p>The type of a path expression is the type of the object
represented by the ending element, which can be one of the following:</p>


<ul><li><p>Persistent field</p>

</li>
<li><p>Single-valued relationship field</p>

</li>
<li><p>Collection-valued relationship field</p>

</li></ul>
<p>For example, the type of the expression <tt>p.salary</tt> is <tt>double</tt> because the
terminating persistent field (<tt>salary</tt>) is a <tt>double</tt>.</p>

<p>In the expression <tt>p.teams</tt>, the terminating element is a collection-valued relationship field (<tt>teams</tt>).
This expression&rsquo;s type is a collection of the abstract schema type named <tt>Team</tt>.
Because <tt>Team</tt> is the abstract schema name for the <tt>Team</tt> entity, this type
maps to the entity. For more information on the type mapping of abstract
schemas, see <a href="#bnbvy">Return Types</a>.</p>



<a name="bnbut"></a><h4>Navigation</h4>
<a name="indexterm-1797"></a><p>A path expression enables the query to navigate to related entities. The terminating
elements of an expression determine whether navigation is allowed. If an expression contains
a single-valued relationship field, the navigation can continue to an object that is
related to the field. However, an expression cannot navigate beyond a persistent field
or a collection-valued relationship field. For example, the expression <tt>p.teams.league.sport</tt> is illegal because <tt>teams</tt>
is a collection-valued relationship field. To reach the <tt>sport</tt> field, the <tt>FROM</tt> clause could
define an identification variable named <tt>t</tt> for the <tt>teams</tt> field:</p>

<pre>FROM Player AS p, IN (p.teams) t
 WHERE t.league.sport = &rsquo;soccer&rsquo;</pre>

<a name="bnbuu"></a><h3><tt>WHERE</tt> Clause</h3>
<a name="indexterm-1798"></a><p><a name="indexterm-1799"></a>The <tt>WHERE</tt> clause specifies a conditional expression that limits the values returned by
the query. The query returns all corresponding values in the data store for
which the conditional expression is <tt>TRUE</tt>. Although usually specified, the <tt>WHERE</tt> clause is optional.
If the <tt>WHERE</tt> clause is omitted, the query returns all values. The high-level
syntax for the <tt>WHERE</tt> clause follows:</p>

<pre>where_clause ::= WHERE conditional_expression</pre>

<a name="bnbuv"></a><h4>Literals</h4>
<a name="indexterm-1800"></a><p>There are four kinds of literals: string, numeric, Boolean, and enum.</p>


<ul><li><p><a name="indexterm-1801"></a><b>String literals</b>: A string literal is enclosed in single quotes:</p>

<pre>'Duke'</pre><p>If a string literal contains a single quote, you indicate the quote by using two single quotes:</p>

<pre>'Duke''s'</pre><p>Like a Java <tt>String</tt>, a string literal in the query language uses the Unicode character encoding.</p>

</li>
<li><p><a name="indexterm-1802"></a><b>Numeric literals</b>: There are two types of numeric literals: exact and approximate.</p>

<p>An exact numeric literal is a numeric value without a decimal point, such as 65, &ndash;233, and +12. Using the Java integer syntax, exact numeric literals support numbers in the range of a Java <tt>long</tt>.</p>

<p>An approximate numeric literal is a numeric value in scientific notation, such as 57., &ndash;85.7, and +2.1. Using the syntax of the Java floating-point literal, approximate numeric literals support numbers in the range of a Java <tt>double</tt>.</p>

</li>
<li><p><a name="indexterm-1803"></a><b>Boolean literals</b>: A Boolean literal is either <tt>TRUE</tt> or <tt>FALSE</tt>. These keywords are not case-sensitive.</p>

</li>
<li><p><a name="indexterm-1804"></a><b>Enum literals</b>: The Java Persistence query language supports the use of enum literals using the Java enum literal syntax. The enum class name must be specified as a fully qualified class name:</p>

<pre>SELECT e
 FROM Employee e
 WHERE e.status = com.xyz.EmployeeStatus.FULL_TIME</pre></li></ul>


<a name="bnbva"></a><h4>Input Parameters</h4>
<a name="indexterm-1805"></a><p><a name="indexterm-1806"></a><a name="indexterm-1807"></a>An input parameter can be either a named parameter or a positional parameter.</p>


<ul><li><p>A named input parameter is designated by a colon (<tt>:</tt>) followed by a string; for example, <tt>:name</tt>.</p>

</li>
<li><p>A positional input parameter is designated by a question mark (<tt>?</tt>) followed by an integer. For example, the first input parameter is <tt>?1</tt>, the second is <tt>?2</tt>, and so forth.</p>

</li></ul>
<p>The following rules apply to input parameters.</p>


<ul><li><p>They can be used only in a <tt>WHERE</tt> or <tt>HAVING</tt> clause.</p>

</li>
<li><p>Positional parameters must be numbered, starting with the integer 1.</p>

</li>
<li><p>Named parameters and positional parameters may not be mixed in a single query.</p>

</li>
<li><p>Named parameters are case-sensitive.</p>

</li></ul>


<a name="bnbvb"></a><h4>Conditional Expressions</h4>
<a name="indexterm-1808"></a><p><a name="indexterm-1809"></a>A <tt>WHERE</tt> clause consists of a conditional expression, which is evaluated from left
to right within a precedence level. You can change the order of evaluation
by using parentheses.</p>



<a name="bnbvc"></a><h4>Operators and Their Precedence</h4>
<a name="indexterm-1810"></a><a name="indexterm-1811"></a><p><a href="#bnbvd">Table&nbsp;34-2</a> lists the query language operators in order of decreasing precedence.</p>

<a name="bnbvd"></a><p class="caption">Table&nbsp;34-2 Query Language Order Precedence</p><table><col width="21%"><col width="78%"><tr><th align="left" valign="top" scope="column"><p>Type</p>

</th>
<th align="left" valign="top" scope="column"><p>Precedence Order</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p>Navigation</p>

</td>
<td align="left" valign="top" scope="row"><p><tt>.</tt> (a
period)</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>Arithmetic</p>

</td>
<td align="left" valign="top" scope="row"><p><tt>+ &ndash;</tt>  (unary)</p>

<p><tt>* /</tt> (multiplication and division)</p>

<p><tt>+ &ndash;</tt> (addition and subtraction)</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>Comparison</p>

</td>
<td align="left" valign="top" scope="row"><p><tt>=</tt></p>

<p><tt>></tt></p>

<p><tt>>=</tt></p>

<p><tt>&lt;</tt></p>

<p><tt>&lt;=</tt></p>

<p><tt>&lt;></tt> (not equal)</p>

<p><tt>[NOT] BETWEEN</tt></p>

<p><tt>[NOT] LIKE</tt></p>

<p><tt>[NOT] IN</tt></p>

<p><tt>IS [NOT] NULL</tt></p>

<p><tt>IS [NOT] EMPTY</tt></p>

<p><tt>[NOT] MEMBER OF</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>Logical</p>

</td>
<td align="left" valign="top" scope="row"><p><tt>NOT</tt></p>

<p><tt>AND</tt></p>

<p><tt>OR</tt></p>

</td>
</tr>
</table>

<a name="bnbve"></a><h4><tt>BETWEEN</tt> Expressions</h4>
<a name="indexterm-1812"></a><p><a name="indexterm-1813"></a>A <tt>BETWEEN</tt> expression determines whether an arithmetic expression falls within a range of
values.</p>

<p>These two expressions are equivalent:</p>

<pre>p.age BETWEEN 15 AND 19
 p.age >= 15 AND p.age &lt;= 19</pre><p>The following two expressions also are equivalent:</p>

<pre>p.age NOT BETWEEN 15 AND 19
 p.age &lt; 15 OR p.age > 19</pre><p>If an arithmetic expression has a <tt>NULL</tt> value, the value of the <tt>BETWEEN</tt>
expression is unknown.</p>



<a name="bnbvf"></a><h4><tt>IN</tt> Expressions</h4>
<a name="indexterm-1814"></a><p>An <tt>IN</tt> expression determines whether a string belongs to a set of string
literals or whether a number belongs to a set of number values.</p>

<p>The path expression must have a string or numeric value. If the
path expression has a <tt>NULL</tt> value, the value of the <tt>IN</tt> expression is unknown.</p>

<p>In the following example, the expression is <tt>TRUE</tt> if the country is <tt>UK</tt>
, but <tt>FALSE</tt> if the country is <tt>Peru</tt>.</p>

<pre>o.country IN ('UK', 'US', 'France')</pre><p>You may also use input parameters:</p>

<pre>o.country IN ('UK', 'US', 'France', :country)</pre>

<a name="bnbvg"></a><h4><tt>LIKE</tt> Expressions</h4>
<a name="indexterm-1815"></a><p><a name="indexterm-1816"></a>A <tt>LIKE</tt> expression determines whether a wildcard pattern matches a string.</p>

<p><a name="indexterm-1817"></a>The path expression must have a string or numeric value. If this value
is <tt>NULL</tt>, the value of the <tt>LIKE</tt> expression is unknown. The pattern value
is a string literal that can contain wildcard characters. The underscore (<tt>_</tt>) wildcard
character represents any single character. The percent (<tt>%</tt>) wildcard character represents zero or more
characters. The <tt>ESCAPE</tt> clause specifies an escape character for the wildcard characters in
the pattern value. <a href="#bnbvh">Table&nbsp;34-3</a> shows some sample <tt>LIKE</tt> expressions.</p>

<a name="bnbvh"></a><p class="caption">Table&nbsp;34-3 <tt>LIKE</tt> Expression Examples</p><table><col width="56%"><col width="21%"><col width="22%"><tr><th align="left" valign="top" scope="column"><p>Expression</p>

</th>
<th align="left" valign="top" scope="column"><p>TRUE</p>

</th>
<th align="left" valign="top" scope="column"><p>FALSE</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>address.phone LIKE '12%3'</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>'123'</tt></p>

<p><tt>'12993'</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>'1234'</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>asentence.word LIKE 'l_se'</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>'lose'</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>'loose'</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>aword.underscored LIKE '\_%' ESCAPE '\'</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>'_foo'</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>'bar'</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>address.phone NOT LIKE '12%3'</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>'1234'</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>'123'</tt></p>

<p><tt>'12993'</tt></p>

</td>
</tr>
</table>

<a name="bnbvi"></a><h4><tt>NULL</tt> Comparison Expressions</h4>
<a name="indexterm-1818"></a><p>A <tt>NULL</tt> comparison expression tests whether a single-valued path expression or an input
parameter has a <tt>NULL</tt> value. Usually, the <tt>NULL</tt> comparison expression is used to
test whether a single-valued relationship has been set:</p>

<pre>SELECT t
 FROM Team t
 WHERE t.league IS NULL</pre><p>This query selects all teams where the league relationship is not set. Note
that the following query is <b>not</b> equivalent:</p>

<pre>SELECT t
 FROM Team t
 WHERE t.league = NULL</pre><p>The comparison with <tt>NULL</tt> using the equals operator (<tt>=</tt>) always returns an unknown
value, even if the relationship is not set. The second query will always
return an empty result.</p>



<a name="bnbvj"></a><h4>Empty Collection Comparison Expressions</h4>
<p><a name="indexterm-1819"></a>The <tt>IS [NOT] EMPTY</tt> comparison expression tests whether a collection-valued path expression has no elements.
In other words, it tests whether a collection-valued relationship has been set.</p>

<p>If the collection-valued path expression is <tt>NULL</tt>, the empty collection comparison expression has
a <tt>NULL</tt> value.</p>

<p>Here is an example that finds all orders that do not have
any line items:</p>

<pre>SELECT o
FROM Order o
WHERE o.lineItems IS EMPTY</pre>

<a name="bnbvk"></a><h4>Collection Member Expressions</h4>
<a name="indexterm-1820"></a><a name="indexterm-1821"></a><a name="indexterm-1822"></a><p>The <tt>[NOT]</tt> <tt>MEMBER [OF]</tt> collection member expression determines whether a value is a member
of a collection. The value and the collection members must have the same
type.</p>

<p>If either the collection-valued or single-valued path expression is unknown, the collection member
expression is unknown. If the collection-valued path expression designates an empty collection, the
collection member expression is <tt>FALSE</tt>.</p>

<p>The <tt>OF</tt> keyword is optional.</p>

<p>The following example tests whether a line item is part of an
order:</p>

<pre>SELECT o
 FROM Order o
 WHERE :lineItem MEMBER OF o.lineItems</pre>

<a name="bnbvl"></a><h4>Subqueries</h4>
<a name="indexterm-1823"></a><p>Subqueries may be used in the <tt>WHERE</tt> or <tt>HAVING</tt> clause of a query.
Subqueries must be surrounded by parentheses.</p>

<p>The following example finds all customers who have placed more than ten orders:</p>

<pre>SELECT c
FROM Customer c
WHERE (SELECT COUNT(o) FROM c.orders o) > 10</pre><p>Subqueries may contain <tt>EXISTS</tt>, <tt>ALL</tt>, and <tt>ANY</tt> expressions.</p>


<ul><li><p><a name="indexterm-1824"></a><tt><b>EXISTS</b></tt> <b>expressions</b>: The <tt>[NOT] EXISTS</tt> expression is used with a subquery and is true only if the result of the subquery consists of one or more values and is false otherwise.</p>

<p>The following example finds all employees whose spouses are also employees:</p>

<pre>SELECT DISTINCT emp
FROM Employee emp
WHERE EXISTS (
    SELECT spouseEmp
    FROM Employee spouseEmp
    WHERE spouseEmp = emp.spouse)</pre></li>
<li><p><a name="indexterm-1825"></a><a name="indexterm-1826"></a><tt><b>ALL</b></tt> <b>and</b> <tt><b>ANY</b></tt> <b>expressions</b>: The <tt>ALL</tt> expression is used with a subquery and is true if all the values returned by the subquery are true or if the subquery is empty.</p>

<p>The <tt>ANY</tt> expression is used with a subquery and is true if some of the values returned by the subquery are true. An <tt>ANY</tt> expression is false if the subquery result is empty or if all the values returned are false. The <tt>SOME</tt> keyword is synonymous with <tt>ANY</tt>.</p>

<p>The <tt>ALL</tt> and <tt>ANY</tt> expressions are used with the <tt>=</tt>, <tt>&lt;</tt>, <tt>&lt;=</tt>, <tt>></tt>, <tt>>=</tt>, and <tt>&lt;></tt> comparison operators.</p>

<p>The following example finds all employees whose salaries are higher than the salaries of the managers in the employee&rsquo;s department:</p>

<pre>SELECT emp
FROM Employee emp
WHERE emp.salary > ALL (
    SELECT m.salary
    FROM Manager m
    WHERE m.department = emp.department)</pre></li></ul>


<a name="bnbvo"></a><h4>Functional Expressions</h4>
<a name="indexterm-1827"></a><a name="indexterm-1828"></a><p>The query language includes several string, arithmetic, and date/time functions that may be
used in the <tt>SELECT</tt>, <tt>WHERE</tt>, or <tt>HAVING</tt> clause of a query. The functions
are listed in <a href="#bnbvp">Table&nbsp;34-4</a>, <a href="#bnbvq">Table&nbsp;34-5</a>, and <a href="#gjjnl">Table&nbsp;34-6</a>.</p>

<p>In <a href="#bnbvp">Table&nbsp;34-4</a>, the <tt>start</tt> and <tt>length</tt> arguments are of type <tt>int</tt> and designate
positions in the <tt>String</tt> argument. The first position in a string is designated
by 1.</p>

<a name="bnbvp"></a><p class="caption">Table&nbsp;34-4 String Expressions</p><table><col width="71%"><col width="28%"><tr><th align="left" valign="top" scope="column"><p>Function Syntax</p>

</th>
<th align="left" valign="top" scope="column"><p>Return Type</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>CONCAT(String, String)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>String</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>LENGTH(String)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>int</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>LOCATE(String, String [, start])</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>int</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>SUBSTRING(String, start, length)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>String</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>TRIM([[LEADING|TRAILING|BOTH] char) FROM] (String)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>String</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>LOWER(String)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>String</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>UPPER(String)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>String</tt></p>

</td>
</tr>
</table><p><a name="indexterm-1829"></a>The <tt>CONCAT</tt> function concatenates two strings into one string.</p>

<p><a name="indexterm-1830"></a>The <tt>LENGTH</tt> function returns the length of a string in characters as an
integer.</p>

<p><a name="indexterm-1831"></a>The <tt>LOCATE</tt> function returns the position of a given string within a string.
This function returns the first position at which the string was found as
an integer. The first argument is the string to be located. The second
argument is the string to be searched. The optional third argument is an
integer that represents the starting string position. By default, <tt>LOCATE</tt> starts at the
beginning of the string. The starting position of a string is <tt>1</tt>. If the
string cannot be located, <tt>LOCATE</tt> returns <tt>0</tt>.</p>

<p><a name="indexterm-1832"></a>The <tt>SUBSTRING</tt> function returns a string that is a substring of the first
argument based on the starting position and length.</p>

<p><a name="indexterm-1833"></a>The <tt>TRIM</tt> function trims the specified character from the beginning and/or end of
a string. If no character is specified, <tt>TRIM</tt> removes spaces or blanks
from the string. If the optional <tt>LEADING</tt> specification is used, <tt>TRIM</tt> removes
only the leading characters from the string. If the optional <tt>TRAILING</tt> specification is used,
<tt>TRIM</tt> removes only the trailing characters from the string. The default is <tt>BOTH</tt>,
which removes the leading and trailing characters from the string.</p>

<p><a name="indexterm-1834"></a><a name="indexterm-1835"></a>The <tt>LOWER</tt> and <tt>UPPER</tt> functions convert a string to lowercase or uppercase,
respectively.</p>

<p>In <a href="#bnbvq">Table&nbsp;34-5</a>, the <tt>number</tt> argument can be an <tt>int</tt>, a <tt>float</tt>, or a
<tt>double</tt>.</p>

<a name="bnbvq"></a><p class="caption">Table&nbsp;34-5 Arithmetic Expressions</p><table><col width="62%"><col width="37%"><tr><th align="left" valign="top" scope="column"><p>Function Syntax</p>

</th>
<th align="left" valign="top" scope="column"><p>Return Type</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>ABS(number)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>int</tt>, <tt>float</tt>, or <tt>double</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>MOD(int, int)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>int</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>SQRT(double)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>double</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>SIZE(Collection)</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>int</tt></p>

</td>
</tr>
</table><p><a name="indexterm-1836"></a>The <tt>ABS</tt> function takes a numeric expression and returns a number of the
same type as the argument.</p>

<p><a name="indexterm-1837"></a>The <tt>MOD</tt> function returns the remainder of the first argument divided by the
second.</p>

<p><a name="indexterm-1838"></a>The <tt>SQRT</tt> function returns the square root of a number.</p>

<p><a name="indexterm-1839"></a>The <tt>SIZE</tt> function returns an integer of the number of elements in the
given collection.</p>

<p>In <a href="#gjjnl">Table&nbsp;34-6</a>, the date/time functions return the date, time, or timestamp on the
database server.</p>

<a name="gjjnl"></a><p class="caption">Table&nbsp;34-6 Date/Time Expressions</p><table><col width="50%"><col width="50%"><tr><th align="left" valign="top" scope="column"><p>Function Syntax</p>

</th>
<th align="left" valign="top" scope="column"><p>Return Type</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>CURRENT_DATE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>java.sql.Date</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>CURRENT_TIME</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>java.sql.Time</tt></p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><tt>CURRENT_TIMESTAMP</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>java.sql.Timestamp</tt></p>

</td>
</tr>
</table>

<a name="gjjnd"></a><h4>Case Expressions</h4>
<a name="indexterm-1840"></a><p>Case expressions change based on a condition, similar to the <tt>case</tt> keyword of
the Java programming language. The <tt>CASE</tt> keyword indicates the start of a case
expression, and the expression is terminated by the <tt>END</tt> keyword. The <tt>WHEN</tt>
and <tt>THEN</tt> keywords define individual conditions, and the <tt>ELSE</tt> keyword defines the default condition
should none of the other conditions be satisfied.</p>

<p>The following query selects the name of a person and a conditional
string, depending on the subtype of the <tt>Person</tt> entity. If the subtype is <tt>Student</tt>,
the string <tt>kid</tt> is returned . If the subtype is <tt>Guardian</tt> or <tt>Staff</tt>, the
string <tt>adult</tt> is returned. If the entity is some other subtype of <tt>Person</tt>,
the string <tt>unknown</tt> is returned.</p>

<pre>SELECT p.name
CASE TYPE(p)
  WHEN Student THEN 'kid'
  WHEN Guardian THEN 'adult'
  WHEN Staff THEN 'adult'
  ELSE 'unknown'
END
FROM Person p</pre><p>The following query sets a discount for various types of customers. Gold-level customers
get a 20% discount, silver-level customers get a 15% discount, bronze-level customers get
a 10% discount, and everyone else gets a 5% discount.</p>

<pre>UPDATE Customer c
SET c.discount = 
  CASE c.level
    WHEN 'Gold' THEN 20
    WHEN 'SILVER' THEN 15
    WHEN 'Bronze' THEN 10
    ELSE 5
  END</pre>

<a name="bnbvr"></a><h4>NULL Values</h4>
<a name="indexterm-1841"></a><p><a name="indexterm-1842"></a>If the target of a reference is not in the persistent store, the
target is <tt>NULL</tt>. For conditional expressions containing <tt>NULL</tt>, the query language uses the semantics
defined by SQL92. Briefly, these semantics are as follows.</p>


<ul><li><p>If a comparison or arithmetic operation has an unknown value, it yields a <tt>NULL</tt> value.</p>

</li>
<li><p>Two <tt>NULL</tt> values are not equal. Comparing two <tt>NULL</tt> values yields an unknown value.</p>

</li>
<li><p>The <tt>IS NULL</tt> test converts a <tt>NULL</tt> persistent field or a single-valued relationship field to <tt>TRUE</tt>. The <tt>IS NOT NULL</tt> test converts them to <tt>FALSE</tt>.</p>

</li>
<li><p><a name="indexterm-1843"></a>Boolean operators and conditional tests use the three-valued logic defined by <a href="#bnbvs">Table&nbsp;34-7</a> and <a href="#bnbvt">Table&nbsp;34-8</a>. (In these tables, T stands for <tt>TRUE</tt>, F for <tt>FALSE</tt>, and U for unknown.)</p>

</li></ul>
<a name="bnbvs"></a><p class="caption">Table&nbsp;34-7 <tt>AND</tt> Operator Logic</p><table><col width="39%"><col width="20%"><col width="19%"><col width="20%"><tr><th align="left" valign="top" scope="column"><p>AND</p>

</th>
<th align="left" valign="top" scope="column"><p>T</p>

</th>
<th align="left" valign="top" scope="column"><p>F</p>

</th>
<th align="left" valign="top" scope="column"><p>U</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>U</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>U</p>

</td>
<td align="left" valign="top" scope="row"><p>U</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>U</p>

</td>
</tr>
</table><a name="bnbvt"></a><p class="caption">Table&nbsp;34-8 <tt>OR</tt> Operator Logic</p><table><col width="39%"><col width="20%"><col width="19%"><col width="20%"><tr><th align="left" valign="top" scope="column"><p>OR</p>

</th>
<th align="left" valign="top" scope="column"><p>T</p>

</th>
<th align="left" valign="top" scope="column"><p>F</p>

</th>
<th align="left" valign="top" scope="column"><p>U</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>U</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>U</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>U</p>

</td>
<td align="left" valign="top" scope="row"><p>U</p>

</td>
</tr>
</table>

<a name="bnbvu"></a><h4>Equality Semantics</h4>
<a name="indexterm-1844"></a><p><a name="indexterm-1845"></a><a name="indexterm-1846"></a>In the query language, only values of the same type can be compared.
However, this rule has one exception: Exact and approximate numeric values can be
compared. In such a comparison, the required type conversion adheres to the rules
of Java numeric promotion.</p>

<p>The query language treats compared values as if they were Java types and
not as if they represented types in the underlying data store. For
example, a persistent field that could be either an integer or a <tt>NULL</tt>
must be designated as an <tt>Integer</tt> object and not as an <tt>int</tt> primitive. This
designation is required because a Java object can be <tt>NULL</tt>, but a primitive
cannot.</p>

<p><a name="indexterm-1847"></a>Two strings are equal only if they contain the same sequence of characters.
Trailing blanks are significant; for example, the strings <tt>'abc'</tt> and <tt>'abc '</tt> are
not equal.</p>

<p><a name="indexterm-1848"></a><a name="indexterm-1849"></a><a name="indexterm-1850"></a><a name="indexterm-1851"></a><a name="indexterm-1852"></a>Two entities of the same abstract schema type are equal only if their
primary keys have the same value. <a href="#bnbvv">Table&nbsp;34-9</a> shows the operator logic of
a negation, and <a href="#bnbvw">Table&nbsp;34-10</a> shows the truth values of conditional tests.</p>

<a name="bnbvv"></a><p class="caption">Table&nbsp;34-9 <tt>NOT</tt> Operator Logic</p><table><col width="44%"><col width="55%"><tr><th align="left" valign="top" scope="column"><p>NOT Value</p>

</th>
<th align="left" valign="top" scope="column"><p>Value</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>U</p>

</td>
<td align="left" valign="top" scope="row"><p>U</p>

</td>
</tr>
</table><a name="bnbvw"></a><p class="caption">Table&nbsp;34-10 Conditional Test</p><table><col width="63%"><col width="12%"><col width="11%"><col width="12%"><tr><th align="left" valign="top" scope="column"><p>Conditional
Test</p>

</th>
<th align="left" valign="top" scope="column"><p>T</p>

</th>
<th align="left" valign="top" scope="column"><p>F</p>

</th>
<th align="left" valign="top" scope="column"><p>U</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p>Expression <tt>IS TRUE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>Expression <tt>IS FALSE</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p>Expression is unknown</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>F</p>

</td>
<td align="left" valign="top" scope="row"><p>T</p>

</td>
</tr>
</table>

<a name="bnbvx"></a><h3><tt>SELECT</tt> Clause</h3>
<a name="indexterm-1853"></a><p>The <tt>SELECT</tt> clause defines the types of the objects or values returned by
the query.</p>



<a name="bnbvy"></a><h4>Return Types</h4>
<p><a name="indexterm-1854"></a>The return type of the <tt>SELECT</tt> clause is defined by the result types
of the select expressions contained within it. If multiple expressions are used, the
result of the query is an <tt>Object[]</tt>, and the elements in the array
correspond to the order of the expressions in the <tt>SELECT</tt> clause and in
type to the result types of each expression.</p>

<p>A <tt>SELECT</tt> clause cannot specify a collection-valued expression. For example, the <tt>SELECT</tt> clause
<tt>p.teams</tt> is invalid because <tt>teams</tt> is a collection. However, the clause in the following
query is valid because the <tt>t</tt> is a single element of the <tt>teams</tt>
collection:</p>

<pre>SELECT t
FROM Player p, IN (p.teams) t</pre><p>The following query is an example of a query with multiple expressions in
the <tt>SELECT</tt> clause:</p>

<pre>SELECT c.name, c.country.name
 FROM customer c
 WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'</pre><p>This query returns a list of <tt>Object[]</tt> elements; the first array element is
a string denoting the customer name, and the second array element is a
string denoting the name of the customer&rsquo;s country.</p>

<p>The result of a query may be the result of an aggregate
function, listed in <a href="#bnbwa">Table&nbsp;34-11</a>.</p>

<a name="bnbwa"></a><p class="caption">Table&nbsp;34-11 Aggregate Functions in Select Statements</p><table><col width="20%"><col width="42%"><col width="36%"><tr><th align="left" valign="top" scope="column"><p>Name</p>

</th>
<th align="left" valign="top" scope="column"><p>Return Type</p>

</th>
<th align="left" valign="top" scope="column"><p>Description</p>

</th>
</tr>
<tr><td align="left" valign="top" scope="row"><p><a name="indexterm-1855"></a><tt>AVG</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>Double</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>Returns the mean average of the fields</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><a name="indexterm-1856"></a><tt>COUNT</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>Long</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>Returns the total
number of results</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><a name="indexterm-1857"></a><tt>MAX</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>The type of the field</p>

</td>
<td align="left" valign="top" scope="row"><p>Returns the highest value in the
result set</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><a name="indexterm-1858"></a><tt>MIN</tt></p>

</td>
<td align="left" valign="top" scope="row"><p>The type of the field</p>

</td>
<td align="left" valign="top" scope="row"><p>Returns the lowest value in the result
set</p>

</td>
</tr>
<tr><td align="left" valign="top" scope="row"><p><a name="indexterm-1859"></a><tt>SUM</tt></p>

</td>
<td align="left" valign="top" scope="row"><p><tt>Long</tt> (for integral fields)</p>

<p><tt>Double</tt> (for floating-point fields)</p>

<p><tt>BigInteger</tt> (for <tt>BigInteger</tt> fields)</p>

<p><tt>BigDecimal</tt> (for <tt>BigDecimal</tt>
fields)</p>

</td>
<td align="left" valign="top" scope="row"><p>Returns the sum of all the values in the result set</p>

</td>
</tr>
</table><p>For select method queries with an aggregate function (<tt>AVG</tt>, <tt>COUNT</tt>, <tt>MAX</tt>, <tt>MIN</tt>,
or <tt>SUM</tt>) in the <tt>SELECT</tt> clause, the following rules apply:</p>


<ul><li><p>The <tt>AVG</tt>, <tt>MAX</tt>, <tt>MIN</tt>, and <tt>SUM</tt> functions return <tt>null</tt> if there are no values to which the function can be applied.</p>

</li>
<li><p>The <tt>COUNT</tt> function returns 0 if there are no values to which the function can be applied.</p>

</li></ul>
<p>The following example returns the average order quantity:</p>

<pre>SELECT AVG(o.quantity)
 FROM Order o</pre><p>The following example returns the total cost of the items ordered by Roxane
Coss:</p>

<pre>SELECT SUM(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'</pre><p>The following example returns the total number of orders:</p>

<pre>SELECT COUNT(o)
FROM Order o</pre><p>The following example returns the total number of items that have prices in
Hal Incandenza&rsquo;s order:</p>

<pre>SELECT COUNT(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = 'Incandenza' AND c.firstname = 'Hal'</pre>

<a name="bnbwb"></a><h4>The <tt>DISTINCT</tt> Keyword</h4>
<p>The <tt>DISTINCT</tt> keyword eliminates duplicate return values. If a query returns a <tt>java.util.Collection</tt>, which
allows duplicates, you must specify the <tt>DISTINCT</tt> keyword to eliminate duplicates.</p>



<a name="bnbwc"></a><h4>Constructor Expressions</h4>
<a name="indexterm-1860"></a><p>Constructor expressions allow you to return Java instances that store a query result
element instead of an <tt>Object[]</tt>.</p>

<p>The following query creates a <tt>CustomerDetail</tt> instance per <tt>Customer</tt> matching the <tt>WHERE</tt> clause. A
<tt>CustomerDetail</tt> stores the customer name and customer&rsquo;s country name. So the query returns
a <tt>List</tt> of <tt>CustomerDetail</tt> instances:</p>

<pre>SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)
 FROM customer c
WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'</pre>

<a name="bnbwd"></a><h3><tt>ORDER BY</tt> Clause</h3>
<a name="indexterm-1861"></a><p>As its name suggests, the <tt>ORDER BY</tt> clause orders the values or objects returned
by the query.</p>

<p>If the <tt>ORDER BY</tt> clause contains multiple elements, the left-to-right sequence of the elements
determines the high-to-low precedence.</p>

<p><a name="indexterm-1862"></a><a name="indexterm-1863"></a>The <tt>ASC</tt> keyword specifies ascending order, the default, and the <tt>DESC</tt> keyword indicates descending
order.</p>

<p>When using the <tt>ORDER BY</tt> clause, the <tt>SELECT</tt> clause must return an orderable set
of objects or values. You cannot order the values or objects for values
or objects not returned by the <tt>SELECT</tt> clause. For example, the following query
is valid because the <tt>ORDER BY</tt> clause uses the objects returned by the <tt>SELECT</tt>
clause:</p>

<pre>SELECT o
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state = 'CA'
ORDER BY o.quantity, o.totalcost</pre><p>The following example is <b>not</b> valid, because the <tt>ORDER BY</tt> clause uses a
value not returned by the <tt>SELECT</tt> clause:</p>

<pre>SELECT p.product_name
FROM Order o, IN(o.lineItems) l JOIN o.customer c
WHERE c.lastname = 'Faehmel' AND c.firstname = 'Robert'
ORDER BY o.quantity</pre>

<a name="bnbwe"></a><h3><tt>GROUP BY</tt> and <tt>HAVING</tt> Clauses</h3>
<a name="indexterm-1864"></a><a name="indexterm-1865"></a><p>The <tt>GROUP BY</tt> clause allows you to group values according to a set of
properties.</p>

<p>The following query groups the customers by their country and returns the number
of customers per country:</p>

<pre>SELECT c.country, COUNT(c)
 FROM Customer c GROUP BY c.country</pre><p>The <tt>HAVING</tt> clause is used with the <tt>GROUP BY</tt> clause to further restrict
the returned result of a query.</p>

<p>The following query groups orders by the status of their customer and returns
the customer status plus the average <tt>totalPrice</tt> for all orders where the corresponding
customers has the same status. In addition, it considers only customers with status
<tt>1</tt>, <tt>2</tt>, or <tt>3</tt>, so orders of other customers are not taken into
account:</p>

<pre>SELECT c.status, AVG(o.totalPrice)
 FROM Order o JOIN o.customer c
GROUP BY c.status HAVING c.status IN (1, 2, 3)</pre>
         </div>
         <div class="navigation">
             <a href="bnbtl.html"><img src="graphics/leftButton.gif" border="0" alt="Previous" title="Previous"></a>
             <a href="p1.html"><img src="graphics/upButton.gif" border="0" alt="Contents" title="Contents"></a>
             <a href="gjitv.html"><img src="graphics/rightButton.gif" border="0" alt="Next" title="Next"></a>
         </div>

         <div class="copyright">
      	    <p>Copyright &copy; 2011, Oracle and/or its affiliates. All rights reserved. <a href="docinfo.html">Legal Notices</a></p>
      	 </div>

      </td>
   </tr>
</tbody>
</table>
</body>
</html>

